Computer method and system for concurrency control using dynamic serialization ordering

ABSTRACT

A mechanism controls concurrency among database transactions through the use of serial ordering relations. The ordering relations are computed dynamically in response to patterns of use. An embodiment of the present invention serializes a transaction that accesses a resource before a transaction that modifies the resource, even if the accessor starts after the modifier starts or commits after the modifier commits. A method of concurrency control for a database transaction in a distributed database system stores an intended use of a database system resource by the database transaction in a serialization graph. A serialization ordering is asserted between the database transaction and other database transactions based on the intended use of the database system resource by the database transaction. The serialization ordering is then communicated to a node in the distributed database system that needs to know the serialization ordering to perform concurrency control. Cycles in the serialization graph are detected based on the asserted serialization order and in order to break such cycles and ensure transaction serializability a database transaction is identified that is a member of a cycle in the serialization graph.

RELATED APPLICATION(S)

This application claims the benefit of U.S. Provisional Application No.60/410,733, filed on Sep. 13, 2002. The entire teachings of the aboveapplication(s) are incorporated herein by reference.

BACKGROUND OF THE INVENTION

It is an object of a database system to allow many users to use the sameinformation at the same time, while making it seem that each user hasexclusive access to all information. The database system should providethis service with minimal loss of performance (latency) and maximaltransaction throughput. The service is generally provided by concurrencycontrol mechanisms, but these mechanisms have problems, including:coordinating conflicting access to shared resources in a distributedenvironment, ensuring serial ordering and preventing deadlocks in adistributed environment and reducing communication and other overheadrequired to achieve these ends.

A number of researchers have published taxonomies of concurrency controlmechanisms (CCMs), to assist in classification and analysis. The generalconsensus divides CCMs at a high level into “pessimistic” concurrencycontrol (PCC) and “optimistic” concurrency control (OCC).

Pessimistic schemes control concurrency by preventing invalid use ofresources. When one transaction attempts to use a resource in a way thatcould possibly invalidate the way another transaction has used theresource, PCC schemes cause the requesting transaction to wait until theresource is available for use without potential conflict.

The advantage of PCC is that it reduces the chance that a transactionwill have to start over from scratch. Two disadvantages of PCC are that(1) there is an increased chance of unnecessary waiting, and (2) thereneeds to be a mechanism to detect deadlocks, or cycles of transactionsall waiting for each other. In general, PCC works best in environmentswith a higher likelihood of transaction conflict, and where it is morecostly to restart transactions.

Optimistic schemes control concurrency by detecting invalid use afterthe fact. They optimize the case where conflict is rare. The basic ideais to divide a transaction's lifetime into three phases: read, validateand publish. During the read phase, a transaction acquires resourceswithout regard to conflict or validity, but it maintains a record of theset of resources it has used (a ReadSet or RS) and the set of resourcesit has modified (a WriteSet or WS). During the validation phase, the OCCexamines the RS of the transaction and decides whether the current stateof those resources has since changed. If the RS has changed, then theoptimistic assumptions of the transaction were proved to have beenwrong, and the system aborts the transaction. Otherwise, the systempublishes the WS, committing the transaction's changes.

The advantages of OCC schemes are that they (1) avoid having a writerwait for a reader in most cases, thereby improving latency andthroughput, and (2) avoid the need to implement deadlock detection. Thedisadvantages are that (1) there is an increased chance of unnecessaryrestarts and of “starvation” (a condition where a transaction iscontinually restarted without making progress), (2) validation in adistributed environment is difficult and can lead to deadlocks, and (3)in order to validate a correct serializable order in a distributedenvironment, validation must occur in two phases—local then global—whichslows things down considerably. In general, OCC works best inenvironments in which there are many more readers than writers, wherethe likelihood of conflict is low, and the cost of restartingtransactions that do experience conflict is acceptable.

Within the general categories of PCC and OCC, there are several majorimplementation techniques, including: locking, time stamping,multi-versioning, and serialization graph algorithms.

The most common locking scheme is called “strict two phase locking”(2PL). In 2PL schemes, a transaction cannot access or use a resourceunless it first acquires a lock. Acquiring a lock gives the transactionpermission to use a resource in a given way, for a given period of time.If a transaction cannot acquire a lock, it must wait, or give up. Lockscome in a variety of types, each lock granting permission for adifferent kind of use. Different types of locks may be compatible orincompatible as applied to the same resource. In general, twotransactions can both acquire read locks on a given record, but cannotboth acquire write locks on the same record. Lock-based schemes providea conflict table, which clarifies which lock types are compatible. Instrict 2PL schemes, transactions hold their locks until they complete.Releasing a lock before completion can improve throughput in somesituations, but opens up the possibility of a cascaded abort (where atransaction that previously committed must be rolled back).

Lock-based schemes have a variety of disadvantages. First, every attemptto use a resource must first acquire a lock. Most of the time, theselocks will prove to be unnecessary; yet acquiring them takes time anduses up memory. Second, in situations where information is cached orreplicated at multiple points in a computationally distributedenvironment, it can be challenging to coordinate locking all thereplicas. Third, in a distributed environment where informationresources can be physically relocated during transactions, it can bedifficult to coordinate accessing the information in its new locationwith the locks in its old location.

An alternative to lock-based mechanisms is called time stamping (TS).The idea is to serialize transactions in the order in which they start.Lock-based mechanisms build on a “wound wait” (WW) scheme. In TS/WWschemes, when an earlier transaction requests a resource held by a latertransaction, the system “wounds” the later transaction, so that theearlier one can proceed. Conversely, when a later transaction requests aresource held by an earlier transaction, the system causes the latertransaction to “wait” for the completion of the earlier transaction.

The advantages of TS/WW systems are that they (1) are deadlock-free, (2)avoid the overhead of lock acquisition, and (3) can make local decisionsabout concurrency control that will be as correct in a globaldistributed environment as they are in a local central environment. Thedisadvantages are that (1) by insisting on serializing in start order,they abort otherwise serializable transaction histories, reducingthroughput and opening up the possibility of starvation, (2) they aresubject to cascaded aborts (a major performance problem) when a latertransaction commits before it can be wounded, (3) they have anadditional disk space and I/O cost in having to stamp records with thestart time of their writer, and (4) comparing time stamps in adistributed environment can be costly with unsynchronized clocks.

Multi-versioning concurrency control (MVCC) utilizes cloned copies of arequested resource. Different copies could be given to differenttransactions to resolve some types of resource conflicts withoutwaiting. When a writer modifies a resource in MVCC, the system clones anew version of the resource and brands it as belonging to the writer.When a reader requests the same resource, it can be given an appropriateversion of the resource. Many systems have built upon the original MVCCscheme. These variations fall roughly into two groups. One group triesto minimize the number of versions, in order to keep down disk storageand I/O requirements. Another group of variations tries to minimizeconflicts (maximize throughput) by keeping as many versions as necessaryto prevent conflicts.

In general, the advantages of MVCC schemes are that they (1) allowreaders and writers to access the same resources concurrently, withoutwaiting, in most cases, (2) avoid lock overhead much of the time, and(3) avoid the problems of cascaded aborts. The disadvantages are thatthey (1) require significantly more disk storage and I/O time, and (2)present challenges in efficiently selecting the appropriate version fora given request.

If transactions executed in serial order, concurrency conflicts wouldnever occur. Each such transaction would be the only transactionexecuting on the system at a given time, and would have exclusive use ofthe system's resources. A new transaction would see the results ofprevious transactions, plus its own changes; and would never see theresults of transactions that had not yet started. In the real world,transactions execute concurrently, accessing and modifying resourcesduring the same periods of time. Yet sometimes, the concurrent executionof multiple transactions in real-world-time can be equivalent to aserial execution order in virtual-database-time.

Serialization graph algorithms (SGAs) control the concurrent operationof temporally overlapping transactions by computing an equivalent serialordering. SGAs try to ‘untangle’ a convoluted sequence of operations bymultiple transactions into a single cohesive thread of execution. SGAsfunction by creating a serialization graph. The nodes in the graphcorrespond to transactions in the system. The arcs of the graphcorrespond to equivalent serial ordering. As arcs are added to thegraph, the algorithms look for cycles. If there are no cycles, then thetransactions have an equivalent serial order and consistency is assured.If a serialization cycle were found, however, then consistency would becompromised if all transactions in the cycle were allowed to commit. Inthis case, the SGA would restore consistency by aborting one or more ofthe transactions forming the cycle.

SGAs can be combined with other mechanisms such as time stamps ormulti-versioning (MV-SGA). MV-SGAs, in particular, have many advantagesover traditional CCMs. Read-only transactions can operate without readlocks and without ever being rolled back. Read-write conflicts can oftenbe resolved without waits, by establishing ordering relationships. Somewrite-write conflicts, between “pure” writes that do not read theaffected data resource (e.g., INSERTs into a relational database table)or between arithmetically commutative operations (e.g.,addition/subtraction), can be avoided as well.

Thus, an effective technique for controlling concurrency and ensuringthe serializability of data base transactions that does not excessivelyimpede overall performance is needed.

SUMMARY OF THE INVENTION

The present invention provides a mechanism for controlling concurrencyamong database transactions through the use of serial orderingrelations. The ordering relations are computed dynamically (i.e., duringa transaction) in response to patterns of use across transactions. Anembodiment of the present invention serializes a transaction thataccesses a resource before a transaction that modifies the resource,even if the accessor starts after the modifier starts or commits afterthe modifier commits.

In distributed environments, consisting of multiple independent databasenodes, serial ordering decisions are made at the locus of resourcecontention (i.e., at the node on which the resource resides). Whendecisions made locally could have an impact on global serialization, thenode communicates ordering information to other nodes on a“need-to-know” basis. As an example, the node on which a transactionoriginates may need to know when that transaction becomes involved in anew serial ordering relationship.

The present invention provides a method of concurrency control for adatabase transaction in a distributed database system by storing anintended (or target) use of a database system resource by the databasetransaction in a serialization graph. A serialization graph is used toassert serialization ordering between the database transaction and otherdatabase transactions, based on the intended (target) use of thedatabase system resource by the database transaction. The serializationordering (as set forth in the local serialization graph) is thencommunicated to a node in the distributed database system that needs toknow the serialization ordering to perform concurrency control and needsto update its serialization graph accordingly. Cycles in theserialization graph are detected based on the asserted serializationorder and a database transaction is identified that is a member of acycle in the serialization graph. Detection of cycles in theserialization graph may be deferred for a period of time.

In one embodiment, the serialization ordering is communicated from afirst node, on which the serialization ordering was originally asserted,to a second node in the distributed database system. The second node isresponsible for ensuring serializability of at least one of the databasetransactions participating in the serialization ordering. The secondnode may be selected according to a policy, for example a policy basedon priority of transaction or resources involved, or based on nodeproperties or other system attributes and criteria.

The present invention provides numerous benefits and advantages overprior art systems for concurrency control. By providing maximallylocalized decisions, the present invention minimizes communicationoverhead and improves performance. The present invention also providesbetter throughput than pure pessimistic schemes by avoiding most of thewait characteristics associated with pure pessimistic schemes. Thepresent invention also avoids most of the aborts and wasted work of pureoptimistic schemes. The dynamic serialization of the present invention(in both distributed and non-distributed database systems) also providesmore flexibility and better throughput than static serialization. Asingle mechanism of the present invention supports multiple classes ofresources at multiple granularities. Integration of SGA and 2PLmechanisms as provided in an embodiment of the present invention offersflexibility not found in pure MVCC SGA mechanism. The present inventionalso offers a single mechanism for both deadlock and cycle detection.

BRIEF DESCRIPTION OF THE DRAWINGS

The foregoing and other objects, features and advantages of theinvention will be apparent from the following more particulardescription of preferred embodiments of the invention, as illustrated inthe accompanying drawings in which like reference characters refer tothe same parts throughout the different views. The drawings are notnecessarily to scale, emphasis instead being placed upon illustratingthe principles of the invention.

FIG. 1 is a serialization graph showing four example transactions.

FIG. 2 is a block diagram of a distributed database system configuredaccording to an embodiment of the present invention.

FIG. 2A is a block diagram of one of the nodes shown in FIG. 2

FIG. 3 is a block diagram of a Transaction Manager data structure.

FIG. 4 is a block diagram of a Transaction data structure.

FIG. 5 is a block diagram of a Resource Usage Record data structure.

FIG. 6 is a block diagram of a Resource Usage Record Index datastructure.

FIG. 7A and FIG. 7B are a flow chart of a procedure for resourceacquisition.

FIG. 8 is a flow chart of a procedure for establishing serial orderings.

FIG. 9 is a flow chart of a procedure for serialization cycle detection.

FIG. 10 is an activity sequence diagram showing communications betweendistributed database components during serialization cycle detectionwith a single host.

FIG. 11 is an activity sequence diagram showing communication betweendistributed database components during serialization cycle detectionwith two hosts.

FIG. 12 is an activity sequence diagram showing communication betweendistributed database components during serialization cycle detectionwith three hosts.

FIG. 13 is a flow chart of a procedure for releasing Resource UsageRecords.

FIG. 14 is a flow chart of a procedure for awakening waitingtransactions.

DETAILED DESCRIPTION OF THE INVENTION

A description of preferred embodiments of the invention follows.Efficiently assuring transaction serializability while avoidingdeadlocks in a distributed environment is a significant problem for theconcurrency control protocols of the prior art. Some approaches use acentralized “hub-and-spoke” scheme, in which a single computationalentity makes all decisions. Other approaches use a federated scheme, inwhich global information affecting serialization is shared among allparticipants. Embodiments of the present invention use a distributeddecision making process. It is neither fully centralized, nor fullyfederated. Instead, each element of a global decision is made locally.Information about serialization is then shared on a “need-to-know”basis.

The following examples show how embodiments of the present inventionefficiently assure serializability in a distributed environment. Theseexamples use some conventions to describe the temporal behavior of thesystem. The explanation of these conventions refer to the terms“DB-Host” and “Node”, which are discussed in detail in the section onthe components relevant to the invention.

-   -   T_(subscript) is used to indicate a particular database        transaction. The subscript has two components. The first        component indicates the transaction id. The second indicates the        host on which the transaction originates    -   T^(superscript) is used to indicate a particular operation. The        superscript may have two components. The first is the action,        such as starting, reading, writing or committing. The second is        the resource being operated upon, such as record #1 on node #1.    -   T_(1H1) ^(S) means that Transaction T₁, originating on DB-Host        #1, starts.    -   T_(1H1) ^(C) means that T₁, originating on DB-Host #1, commits.    -   T_(1H1) ^(A) means that T₁, originating on DB-Host #1, aborts.    -   T_(1H1) ^(R1N2) means that T₁, originating on DB-Host #1, reads        Resource #1 on Node #2.    -   T_(1H2) ^(M1N3) means that T₁, originating on DB-Host #2,        modifies Resource #1 on Node #3.    -   T_(1H1) ^(R1N2) (T_(1H1)→T_(2H1)) means that, as a result of its        action, T₁ is serialized before T₂.

When serialization cycles are formed through local interactions, cycledetection can occur locally:

-   T_(1H1) ^(S), T_(2H1) ^(S), T_(1H1) ^(R1N1), T_(2H1) ^(M1N1)    (T_(1H1)→T_(2H1)), T_(2H1) ^(R2N1), T_(1H1) ^(M2N1)    (T_(2H1)→T_(1H1))    Here, T₁ starts on DB-Host #1. Then T₂ starts on DB-Host #1. Then T₁    reads Resource #1 on Node #1, where Node #1 is the same node as    DB-Host #1. Then T₂ modifies Resource #1 on Node #1. At this point,    the system serializes T₂ after T₁. This is because T₁ reads a value    modified by T₂, but did not retrieve the modified version. This can    only be equivalent to a serial ordering in which T₁ occurs in its    entirety, followed by T₂ in its entirety; because if T₂ occurred    first, then T₁ would have read the value produced by its    modification of Resource #1. Next, T₂ reads a different Resource #2    on Node #1. Then T₁ modifies that resource. By similar logic, T₂    must serialize before T₁ here, because if T₁ came first, then T₂    would have read the value produced by T₁, and that did not happen.

At this point, a serialization cycle has formed. T₁ must precede T₂based on the fourth step. But T₁ must follow T₂ based on the last step.This cycle would allow inconsistencies. So the system must abort one orboth of the transactions.

Serialization cycles can also be formed remotely:

-   T_(1H1) ^(S), T_(2H1) ^(S), T_(1H1) ^(R1N2), T_(2H1) ^(M1N2)    (T_(1H1)→T_(2H1)), T_(2H1) ^(R2N2), T_(1H1) ^(M2N2)    (T_(2H1)→T_(1H1))    This situation is almost identical to the one above, except that    Node #2 and Node #3 are not the same as DB-Host #1. Based on the    “need-to-know” rule, Node #2 and Node #3 will communicate the    serialization edges above to DB-Host #1 (which is the originator of    both transactions). DB-Host #1 will detect the serialization cycle,    and one or both of the transactions will need to be aborted as    discussed above.    Even though a transaction may have committed, it can still    participate in a serialization cycle. Information about such    transactions needs to be retained until it can be safely discarded,    which is discussed below.    -   1. T_(1H1) ^(S), T_(2H1) ^(S),    -   2. T_(1H1) ^(R1N2), T_(1H1) ^(M2N2), T_(2H1) ^(R2N2)        (T2_(H1)→T_(1H1)),    -   3. T_(1H1) ^(C),    -   4. T_(3H1) ^(S) (T_(1H1)→T_(3H1)),    -   5. T_(2H1) ^(M3N2), T_(3H1) ^(R3N2) (T_(3H1)→T_(2H1))        In Step 4, the system serializes T₁ before T₃ because T₁        committed before T₃ started. At the end of Step 5, DB-Host #1        will detect the cycle T₂→T₁→T₃→T₂. In order to break this cycle        and prevent inconsistencies, one of the active transactions in        the cycle (T₂ or T₃) must be aborted (T₁ cannot be aborted,        having already committed).

It is possible for a single serialization ordering between twotransactions to simultaneously create multiple serialization cycles. Asimple example of this involves four transactions with a history asshown below (see FIG. 1):

-   -   1. T_(1H1) ^(S), T_(2H1) ^(S), T_(3H1) ^(S), T_(4H1) ^(S),    -   2. T_(1H1) ^(R1N2), T_(2H1) ^(M1N2) (T_(1H1)→T_(2H1)),    -   3. T_(1H1) ^(R2N2), T_(3H1) ^(M2N2) (T_(1H1)→T_(3H1)),    -   4. T_(2H1) ^(R3N2), T_(4H1) ^(M3N2) (T_(2H1)→T_(4H1)),    -   5. T_(3H1) ^(R4N2), T_(4H1) ^(M4N2) (T_(3H1)→T_(4H1)),    -   6. T_(4H1) ^(R5N2), T_(1H1) ^(M5N2) (T_(4H1)→T_(1H1)),        After the final step, two cycles are created: (T₁→T₂→T₄→T₁ and        T₁→T₃→T₄→T₁). In a similar way, it is possible to close three or        more cycles simultaneously. In the example shown, both cycles        involved three nodes. But it is possible for simultaneously        created cycles to have different length perimeters.

If serialization cycles are always broken by aborting the transactionwhose action would create the cycle, then it doesn't matter thatmultiple cycles may be formed simultaneously. If the system breaks aserialization cycle by selecting a transaction victim based on its age,its priority, or other factors, then the presence of multiple cycles canaffect the operation of the invention. In the example above, if thesystem chose to abort T₂, it would still be left with the second cycleT₁→T₃→T₄→T₁.

It is possible for a serialization cycle to develop across three or moreDB-Host nodes. A simple example of this involves three transactions witha history as shown below:

-   -   1. T_(1H1) ^(S), T_(2H2) ^(S), T_(3H3) ^(S),    -   2. T_(1H1) ^(R1N4), T_(2H1) ^(M1N4) (T_(1H1)→T_(2H2)),    -   3. T_(2H2) ^(R2N5), T_(3H1) ^(M2N5) (T_(2H2)→T_(3H3)),    -   4. T_(3H3) ^(R3N6), T_(1H1) ^(M3N6) (T_(3H3)→T_(1H1))        At the end of Step #2, the serialization information among the        nodes is as follows:    -   DB-Host #1: T₁→T₂    -   DB-Host #2: T₁→T₂    -   DB-Host #3: null        At the end of Step #3, the serialization information among the        nodes is:    -   DB-Host #1: T₁→T₂    -   DB-Host #2: T₁→T₂→T₃    -   DB-Host #3: T₂→T₃        At the end of Step #4, the serialization information among the        nodes is:    -   DB-Host #1: T₃→T₁→T₂    -   DB-Host #2: T₁→T₂→T₃    -   DB-Host #3: T₂→T₃→T₁        Although it is clear that a cycle T₁→T₂→T₃→T₁ has formed after        Step #4, the serialization information local to each DB-Host        node is acyclic.        In order to detect cycles of this kind, it is important to        follow the “need-to-know” rule on host nodes as well as on local        nodes. After Step #3, on DB-Host #2, a new ordering relation has        been transitively imposed. Looking at this closely:    -   Before Step #3, DB-Host #2 had: T_(1H1)→T_(2H2)    -   After Step #3, DB-Host #2 had: T_(1H1)→T_(2H2)→T_(3H3)        By transitivity, Step #3 implicitly imposes the ordering        relation T_(1H1)→T_(3H3). Neither T_(1H1) nor T_(3H3) are local        to DB-Host #2. Therefore, the new ordering relationship between        them must be sent to both DB-Host #1 and DB-Host #3, as they        need to know about new ordering relationships affecting the        transactions they originate. After this communication (between        Step #3 and Step #4), the serialization information among the        nodes is:    -   DB-Host #1: T₁→{T₂, T₃}    -   DB-Host #2: T₁→T₂→T₃    -   DB-Host #3: {T₂, T₁}→T₃        Then, after Step #4, the serialization information among the        nodes is:    -   DB-Host #1: T₃→T₁→{T₂, T₃}    -   DB-Host #2: T₁→T₂→T₃    -   DB-Host #3: {T₂, T₁}→T₃→T₁        Both DB-Host #1 and DB-Host #3 could then detect the formation        of a cycle.

The present invention provides asserting and communicating serialordering of transactions, detecting conflicts for database systemresources and identifying victims to resolve the conflicts. A method ofconcurrency control for a database transaction in a distributed databasesystem comprises storing an intended use of a database system resourceby the database transaction in a serialization graph. A serializationordering between the database transaction and other databasetransactions is asserted based on the intended use of the databasesystem resource by the database transaction. The serialization orderingis communicated to another node in the distributed database system thatuses the serialization ordering to perform concurrency control ondatabase transactions it manages. Cycles are detected in theserialization graph based on the asserted serialization order and adatabase transaction that is a member of a cycle in the serializationgraph is identified. In this way, the present invention providesdistributed concurrency control using serialization ordering.

The invention has several advantages over traditional approaches toconcurrency control in a distributed environment. Some approaches todistributed concurrency control use a centralized “hub-and-spoke”scheme, in which a single computational entity makes all concurrencycontrol decisions. This requires all other computational entities totransmit their concurrency-related information and wait for a ‘verdict’on whether it is OK to proceed. If the single central decision makerbecomes unavailable, then the whole system must wait to resolvequestions of serialization. Another approach to distributed concurrencycontrol uses a federated scheme, in which global information affectingserialization is shared among all participants. A federated system ismore resilient to failure and delay. But it pays a price in highercommunication costs and in having to control periods of time in whichinformation is not uniformly distributed.

The present invention uses a distributed decision making process.Relative to a centralized “hub-and-spoke” scheme, the invention lowersthe risk that a single failure will cripple the whole DBMS. Also, thepresent invention's ability to make decisions locally, where possible,reduces the amount of communication between the elements of the system,so that performance is better than in the centralized scheme. Relativeto a federated scheme, the present invention's “need-to-know” approachreduces the amount of communication required, improving performance. The“need-to-know” approach also simplifies the challenge to a federatedscheme in coordinating the uniform distribution of concurrency-relatedinformation.

Pessimistic Concurrency Control Mechanisms (PCCMs) control concurrencyby avoiding conflict. When a transaction tries to use a resource in away that could possibly cause inconsistency, PCCMs force it to waituntil no conflict could possibly occur. Optimistic Concurrency ControlMechanisms (OCCMs) use resources as requested, without regard toconcurrency control. When concurrency conflicts arise, OCCMs resolve theconflict by aborting transactions.

The present invention avoids most of the wait characteristic of PCCMs.It also avoids most of the aborts and wasted work of OCCMs. Inenvironments that have occasional resource contention, but relativelyinfrequent serialization cycle or deadlock, the present inventionprovides higher throughput than either pure pessimistic or pureoptimistic schemes.

Most MV-SGA schemes use a static criterion to decide serializationordering. A common choice is to serialize based on transaction startorder, using either a time stamp or a sequentially assigned identifier.Another common choice is to serialize based on commit order, so thattransactions that commit earlier, serialized before those that commitlater.

In one embodiment of the invention, serial ordering between transactionsis assigned based on their dynamic patterns of use. Sometimes this willresult in serializations that would be inadmissible in start-order orcommit-order schemes. For example, consider the following transactionhistory:

-   T₁ ^(starts), T₂ ^(starts), T₂ ^(reads R1), T₁ ^(modifies R1), T₁    ^(commits), T₂ ^(does a long computation), T₂ ^(commits)    Here, T₁ starts before T₂. Approaches that always serialize based on    start order would have a problem after the fourth step, T₁    ^(modifies R1). If T₂ follows T₁ (because it starts after T₁), then    when T₂ read R1 in the third step, it should have seen the result of    T₁'s modification. But that modification had not occurred yet. So if    a static start-order based mechanism insists that T₂ follows T₁,    then it must abort one of the two transactions, so that either T₂    does not read an inconsistent value, or T₁ does not produce an    inconsistent value.

In the history above, T₁ commits before T₂. Approaches that alwaysserialize based on commit order would also have a problem after T₁^(modifies R1). If T₂ follows T₁, then when it read R1, it should haveseen the result of T₁'s modification. Since it did not, T₁ cannot beallowed to commit earlier than T₂. A commit-order-always based mechanismmust either (a) abort one of the two transactions, or (b) cause T₁ towait until T₂ completes. Of course, if T₂ takes a long time to commit,then T₁ will have to wait a long time before it can read R1.

In contrast, one embodiment of the present invention assigns serialordering between transactions based on their dynamic patterns of use. Inthe history above, an embodiment of the present invention would decidethat T₁ follows T₂ at the point that T₁ ^(modifies R1), based on theprinciple that readers serialize before writers. By decouplingserialization order from either transaction start order or transactioncommit order, the present invention is able to allow transactionhistories like the one above, without waits or aborts.

Most databases offer the ability to control concurrency of multipleclasses of resources at multiple levels of granularity. For example,relational databases frequently offer both record level locking andtable level locking. Since records are part of tables in relationaldatabases, locking a table may conflict with locking a record in thetable. The total number of concurrency conflict situations that canarise in such systems is the product of the number of different types ofusage locks on each different resource class and granularity.Concurrency control in these systems gets complex quickly. It is hardfor users to understand. It is also hard for DBMS authors to extendtheir concurrency control mechanisms to cover additional classes andgranularities of resources, as the number of possible interactions growsexponentially.

By focusing exclusively on serial ordering relations, the presentinvention provides a single mechanism for coordinating concurrencyacross resource classes and granularities.

As in traditional MVCC mechanisms, read-write conflicts can be resolvedthrough serialization choices, so that resource users experience feweraborts and less waiting. When two transactions both wish to access andmodify the same resource, the system must either abort one transactionor force one to wait for the completion of the other. Whereas pure MVCCSGA mechanisms would abort one of the transactions in this case, theinvention allows the possibility of resolving the conflict throughwaiting when the transactions operate with the Read Committed isolationlevel. The invention is able to offer this benefit through theintegration of serialization ordering and two-phase locking.

The invention integrates SGA and 2PL mechanisms. Write-Write conflicts,for example, can be resolved by waiting on a lock. In addition tooffering this flexibility, the invention provides a single mechanism fordetecting both deadlocks and serialization cycles.

FIG. 2 is a block diagram of a distributed database system configuredaccording to one embodiment of the present invention. A distributeddatabase consists of many nodes, which may have different capabilities.A DB-Host 100 node is capable of originating new transactions. Thesetransactions can execute queries, which may acquire resources thatreside locally to the DB-Host or remotely on another node. The DB-Host100 comprises three software components. The Query Execution Manager(QEMgr) component 101 is capable of dividing a query into snippets, someof which may be executed local to the DB-Host 100, and some of which maybe executed on remote nodes. A snippet is a piece of a query, which maycontain one or more database operations. The Resource Usage Manager(RUMgr) component 102 is responsible for coordinating concurrent use ofresources local to the DB-Host 100. The Transaction Manager (TxMgr)component 103 is responsible for transaction management functions,including starting, committing, aborting, and recovering transactions.In addition, the TxMgr is responsible for establishing a linearserialization order for all transactions (whether originating locally orremotely to the DB-Host 100) that interact with those originating on theDB-Host 100.

A database snippet processing unit (DB-SPU) 110 node is capable ofexecuting query snippets received from DB-Host 100 nodes. A DB-SPU 110node need not be capable of originating new transactions. A DB-SPU 110node comprises three software components. The Query Execution Manager(SpuQEMgr) component 111 is capable of receiving and processing a querysnippet received from another node. The SpuQEMgr 111 may need to uselocal resources to process a query snippet, and coordinates the use ofsuch resource by interacting with the Resource Usage Manager (SpuRUMgr)component 112. The SpuRUMgr 112 keeps track of which transactions usewhich resources in which ways. When two transactions use the sameresource, their use may impose a serial ordering on their execution. Ifso, the SpuRUMgr 112 communicates this ordering to the TransactionManager (SpuTxMgr) component 113. The SpuTxMgr 113 is responsible formaintaining a local view of the serial ordering relationship amongtransactions operating on the DB-SPU 110 node. If the SpuRUMgr 112informs the SpuTxMgr 113 of a new serial ordering relationship betweentwo transactions, and if one or both of those transactions originate ondifferent nodes, then the SpuTxMgr 113 sends a message to the TxMgr 103component on the nodes on which the newly ordered transactionsoriginated.

FIG. 2A is a block diagram of one of the DB-SPU 110 nodes shown in FIG.2. The DB-SPU 110 includes a memory 120, a central processing unit 126,a network interface component 122 for coupling to a data communicationnetwork and a storage controller interface 124 (e.g., an IDE controllerinterface) for coupling to a storage controller (e.g., IDE disk drivecontroller).

FIG. 3 is a block diagram of a Transaction Manager data structure 200used by the TxMgr 103 and SpuTxMgr 113. The TxMgr data structure 200contains a Vector of Transactions (Vector) field 201, which is asequence of transaction data structures 300 (FIG. 4), contiguous inmemory. Individual transactions 300 can be referenced by their indexwithin this Vector 201. The following four fields are associated withthis Vector 201. The Current Number of Transactions in the Vector(curCount) field 202 keeps track of the number of transactionsrepresented within the Vector 201. The size of the Vector 201 istypically larger than the curCount 202. The Index of the OldestTransaction in the Vector (oldestIndex) field 203 points into the Vector201 to the oldest transaction. At a given time, the oldest transactionon the system is not necessarily in the first slot in the Vector 201.The Index of the Oldest Active Transaction (oldestActive) field 204points into the Vector 201 to the oldest active transaction.

The distinction between the oldest transaction and the oldest activetransaction is subtle and important. In one embodiment of the presentinvention, information associated with a transaction “A” may need to bemaintained after it commits, until the system is certain that no newserialization edges can be created that would lead to a cycle involvingthis transaction. This is the case when (i) there is no other activetransaction that started before transaction “A” committed, and (ii)there is no other committed transaction that serializes (via a path ofone or more serialization edges) before transaction “A”. For thisreason, the oldestActive field 204 may indicate a different transactionthan the oldestIndex field 203. The Index of Newest Transaction inVector (newestIndex) field 205 points to the most recent transaction300. An embodiment of the invention uses the oldestIndex 203 and thenewestIndex 205 to loop through every transaction 300 on the system.Finally, the TxMgr data structure 200 contains other information 206useful for transaction processing, which is not necessarily utilized bythe present invention.

FIG. 4 is a block diagram of a Transaction data structure 300. The TIDfield 301 is a symbol that uniquely identifies a transaction throughoutall time. The State Information (state) field 302 describes the state ofthe transaction 300, including whether it is active, waiting, committed,or aborted. The Transaction Start Time (startTime) field 307 and theTransaction End Time (endTime) field 308 are used in embodiments of thepresent invention to determine when the TxMgr 103 and the SpuTxMgr 113can release the resources associated with a transaction 300. The rule inthis regard is that a transaction 300 that commits as of a certainendTime 308 must retain its resources until there is no other activetransaction 300 whose startTime 307 is less than that endTime 308.

The Bit Vector of Following Transactions (followers) field 312 maintainsa record of which other transactions 300 follow the given transaction300 in serialization order. Each bit position in the vector isinterpreted as an index of another transaction within the TxMgr datastructure's 200 Vector of Transactions 201. For example, if atransaction “A” had followers with a bit set in the third position, thatwould mean that transaction “A” precedes the transaction found at index3 into the TxMgr data structure's 200 Vector of Transactions 201. Theset of all transactions 300 and their followers 312 forms aserialization graph at each DB-Host 100 and DB-SPU 110 node in thesystem.

A “read-only transaction” is a transaction 300 that performs no updateoperations. As such, its resource use never conflicts with that ofanother transaction. The present invention avoids concurrency controlprocessing and overhead for read-only transactions that requireRepeatable Read isolation (or a weaker isolation level). For read-onlytransactions requiring Serializable isolation, resource usage andserialization ordering need to be tracked to ensure that the transactionsees a state consistent with the serialization ordering relationshipsamong update transactions. The IsReadOnly field 313 keeps a record ofwhether a transaction 300 is a read-only transaction.

The Index of Next Transaction in Start Order (next) field 314 maintainsa thread through the TxMgr data structure's 200 Vector of Transactions201, sorted by startTime 307. This field is used to loop through all thetransactions. Occasionally, transaction throughput can be improved byhaving one transaction wait for the completion of another transactionbefore acquiring a resource. The waitFor field 315 indicates thetransaction 300 (if any) whose completion is required before a resourcerequest can be honored. When one transaction waits for another, theTxMgr data structure 200 records in a field 316 the query plan thatshould be restarted after waiting. The TxMgr data structure also recordsin field 317 an indication (identifier) of the DB-Host 100 that startedthis transaction 300. When a new serialization ordering relationship isestablished between two transactions on a given node (100, 110), theTxMgr (103, 113) on that node communicates the new ordering informationon a “need-to-know” basis.

Invisibility List 303 information (304, 305, 306) is used to controlwhich version of a record is visible to a transaction at a given time.The Low Water Mark 309 and High Water Mark 310 are used to expediterollback processing by marking the affected portions of the databasefile. The fields (303–306, 309, 310) are included for completeness, butare not essential to the operation of the present invention. Finally,there is other information 311 useful for transaction processing, whichmay not be used by the present invention.

FIG. 5 is a block diagram of a Resource Usage Record data structure 400that records the use of a resource. A Resource Usage Record datastructure 400 contains four fields. The ResourceID 401 field identifiesthe resource used. If the resource is a record in the database, theResourceID is a value that uniquely identifies the record. In amulti-versioning system with multiple versions of the same record, eachversion would have the same ResourceID. If the resource is a table inthe database, the ResourceID is a value that uniquely identifies thetable. In one embodiment of the present invention, a 64-bit quantity isused to identify resources, but any unique value may be used. TheResourceUserID 402 field uniquely identifies the transaction that usesthe resource. The UsageType 403 field encapsulates the way in which theresource is used. The most common types of usage are reading andwriting. Other usage types are possible as well, such as insert/create,add/subtract, and multiply/divide. The optional Qualifier 404 field, inthe case of a table, identifies a subset of the records in the table bya predicate on the contents of the records. For an SQL query of the form“SELECT * from Employee WHERE Age >55”, for example, the Qualifier 404is a representation of the WHERE clause “Age >55”. The Qualifier 404 isblank if no proper subset of the table can be identified (in which casethe UsageType 403 is treated as applying to potentially all records ofthe table), or if the ResourceID 401 identifies an individual record.

FIG. 6 is a block diagram of a Resource Usage Record Index datastructure used to index records of resource use. A Resource Usage RecordIndex data structure 510 is used by the RUMgr 102 and the SpuRUMgr 112to rapidly locate instances of Resource Usage Record data structures400. It contains a Resource Class ID 511 that uniquely identifies aresource class. If the resource is a record belonging to a table, thenthe resource class could be the table. The Resource Usage Record Indexdata structure 510 also contains a sequence 513 of Resource Usage Recordnodes (RURNode) 514. Each RURNode 514 contains two fields. The ResourceUsage Record Pointer 515 field denotes a Resource Usage Record 400. TheFirst Resource ID Referenced 516 field is used as a primary sort on thesequence 513 of RURNodes. Total number of RURNodes 514 in the sequence513 is indicated in Number field 512.

FIGS. 7A and 7B are a flow chart of a procedure for resourceacquisition. The procedure for normal resource allocation takes threeinputs: 1) a requesting transaction, 2) a requested set of resources ofa given class and 3) intended use of the requested resources. Thepresent invention controls concurrency by registering intended use witha node's RUMgr (102, 112). This serves a function that is similar toacquiring a lock in 2PL-based systems. However, unlike a lock, aResource Usage Record 400 does not necessarily block access. It merelyrecords an intended use of a resource. There may be several levels ofresource usage, from those requiring the highest level of isolation, tothose not requiring isolation at all. The levels are, in the order fromhighest to lowest: serializable, repeatable read, read committed, anddirty read (sometimes referred to as “read uncommitted”).

The present invention allows several modes of resource acquisition.First, because resource acquisition of any type requires processing timeand memory, it is recognized that several classes of use do not requirechecking at all. Transactions that are known to be read-only can neverexperience concurrency conflicts. Therefore, read-only transactions donot need to acquire their resources or create RURs when operating at theRepeatable Read isolation level or below. In rare circumstances,read-only transactions operating at the Serializable isolation level mayneed to be aborted to guarantee a consistent view. In SQL-92 conformantrelational databases, there is a specific command to set a transactionto be read-only. In addition, any single SELECT statement outside thescope of an explicit transaction is also known to be read-only.Transactions that do nothing more than load new data (insert-only, noreading) can also avoid the expense of acquiring RURs. Any class ofresource that is not shared does not require RURs. Examples of suchresource classes are: (a) user-defined temporary tables; (b) tables thatan optimizer/planner determines have a lifetime limited to a giventransaction; and (c) temporary tables created by an optimizer/planner asintermediate results to be dropped no later than the end of thetransaction.

The normal mode of acquisition is to record read or write intent on aresource of a given class, such as a record in a table, a whole table,or some other resource granularity such as a subset of the records in atable matching a qualifier predicate. Other modes of acquisition includereverse mode. The intent of reverse mode acquisition is to reduce theRUR overhead for environments with relatively few update transactions.

In order to control concurrent use of a resource, one embodiment of thepresent invention provides transactions an ability to acquire the rightto use resources in particular ways. Acquiring rights involves severalsteps: 1) checking to see if other transactions are using the sameresource(s) in conflicting ways, 2) potentially waiting until therequestor can acquire the right to use the resource(s), 3) establishingserial ordering relationships in order to resolve conflicts and 4)leaving a record of the intended use, to help resolve future conflicts.

As introduced above, the procedure for acquiring rights takes threeinputs: 1) the requesting transaction, 2) the resources being requestedand 3) the intended use of those resources. The resources may be recordsin a table, tables in the database, or any other resource. The intendeduse may be the intention to retrieve the resources (read), to modify theresources (write), or any other intended use.

The procedure returns when the requesting transaction has the right touse the specified resources in the specified way. It takes a cautiouslyoptimistic approach in making this determination. If it can determinelocally that the requester must wait to acquire the right to use, thenit does not return until the right can be granted. If it cannot make alocal determination, it conditionally grants the caller the right to usethe resources, but it may later revoke the right and abort thetransaction.

The process begins at Step 601 where a check is made to see whether theintended resources actually exist. If not, then the requestor is giventhe right to use them by returning true (Step 699). Step 602 checks tosee whether the intended use requires concurrency control or not. Ifnot, then the requestor is given the right to use the resources byreturning true (Step 699). A read-only transaction, for example, wouldnot require any special checking to acquire the right to read a record.

After the quick checks mentioned above (Steps 601, 602), the proceduresets a variable to indicate that it is not necessary to wait to acquirethe resources (Step 603). This variable may be reset later on. It ischecked at the end to see whether the requester must wait. Next, theprocedure, at Step 604, locates the access method for the specifiedclass of resources. The access method is used to rapidly locate anyexisting RURs that may match the input list of resources.

The procedure then loops over each requested resource (Step 605). Foreach requested resource, the procedure sets a variable indicating aNeedAnRUR to true (Step 606) and uses the access method to find all RURsinvolving that resource at Step 607. For each such RUR (Step 608), theprocedure performs a series of tests after getting the existing user atStep 613 (FIG. 7B). At Step 614, if the existing user (the TID in thenext RUR) has aborted, the procedure continues examining the next RURapplying to the given resource. At Step 615, if the existing user is thesame as the requestor, then the requestor has already acquired the rightto use the resource. If the intended uses are the same (step 623), theprocedure can return immediately. If the intended use is morerestrictive than the previous use, for example, if the current requestis to write a record while the previous request was to read the samerecord, then (a) remember that we may need to upgrade the use on theexisting RUR, and (b) continue checking. If the intended use must followthe existing use (Step 616), then call the procedure AssertOrder (Step617) to assert that the requestor must follow the transaction indicatedin the RUR. This case would occur if the request were to modify a recordthat had been read by another transaction.

If the intended use must precede the existing use (Step 618), then callthe procedure AssertOrder (Step 619) to assert that the requestor mustprecede the transaction indicated in the RUR. This case would occur ifthe request were to read a record that had been modified by anothertransaction.

The procedure determines if the intended use must wait (Step 620) forany of the existing uses. This would be the case, for example, if theintention was to modify a record that was already modified by anothertransaction, assuming that the requester operated at either the ReadCommitted or Repeatable Read isolation level. In this case, theprocedure AssertOrder is called (Step 621) to assert that the requestormust follow the transaction indicated in the RUR. Then, modify therequestor transaction to indicate that it is waiting for the completionof the transaction identified by the RUR (Step 622).

Each RUR for a given resource is processed as above, each beginning withlooping back to Step 608.

After examining all RURs for the given resource, if the requestoralready had an RUR (Step 609) and if the current intended use is morerestrictive, then the procedure upgrades the intended use of theexisting RUR to the current intended use. For example, if the existingRUR's intended use was the right to read a record and if the currentintended use is the right to write the record, then the procedureupdates the existing RUR's intended use from read to write.

After examining all RURs for the given resource, if the requestor didnot already have an RUR, then the procedure creates an RUR (611) todescribe the intended use of the given resource. The procedure then addsthe RUR into the access method, so that it can be found by laterrequests.

If the requestor transaction needs to wait for the completion of thetransaction identified by the RUR (Step 610) then suspend therequestor's thread of execution at Step 612, return false (Step 697) andloop back to Step 605 for next requested resource, if any.

A pseudo-code representation of the procedure for resource acquisitionfollows:

AcquireRight(requestor, Resources<ofAclass>, intendedUse) If resourcesdo not exist, or do not require control, return true If the intendedUsedoes not require control, return true MustWait = false Find theAccessMethod for the Resource Class Foreach resource NeedAnRUR = true;Get the specials: ID, Creator, Deleter Foreach usageRecord for the givenresource Get the ExistingUser If the ExistingUser has aborted, continueIf the ExistingUser is the RequestingUser Update usageRecord to includeintention NeedAnRUR = false; Continue If (intendedUse must followexistingUse) AssertOrder(existUser, intendUser) If (intendedUse mustprecede existingUse) AssertOrder(intendUser, existUser) If (intendedUsemust wait for existingUse) AssertOrder(existUser, intendUser) MustWait =true If (NeedAnRUR) insertRURIntoAccessMethod(resourceID, UserID,IntendedUse) If (MustWait) Suspend requestor Return false

It takes valuable time and space to create and check Resource UsageRecords (RURs). For read-intensive transactions that are not declared tobe read-only, dealing with RURs can mean a significant reduction inperformance. One way to reduce this overhead is for a read-intensivetransaction to use resources at a larger level of granularity. If atransaction declares its intention to read a whole table of records,then transactions that update records in the table will serialize afterthe reader, even if the reader never read the specific records modifiedby the update transactions.

The optional Qualifier field 404 in RUR allows for serializationordering at an intermediate granularity between individual records andentire tables. If a read operation on a table of financial transactionsis only looking for the past week's transactions and a modify operationon the same table is only deleting transactions more than 60 days old,then the corresponding qualifiers are known not to overlap (in the sensethat there cannot be any records in the table that match bothqualifiers) and no serialization edge is asserted between these twotransactions. If the two qualifiers cannot be shown not to overlap (ifthe first transaction were looking for transactions for a particularaccount, say, rather than by date) then a serialization edge is assertedjust as if the two transactions were reading and modifying the entiretable. This approach reduces the overhead associated with trackingoperations at the record level without losing all of the concurrencyadvantages.

Reverse mode resource acquisition is another technique for obtaining theadvantages of using resources at larger levels of granularity (lessoverhead) while preserving some of the concurrency advantages ofrecord-level usage. Typically, when transactions read records in a giventable, they first check to see if any active transactions have modifiedor are waiting to modify records in the table. If there are no actual orpotential updaters, then the readers operate at the granularity of thetable, otherwise they acquire RURs at the level of individual records.

When transactions update records in a table, they first check to see ifthere are any readers operating at the granularity of the table. If so,the writers either serialize after such readers, or wait for thecompletion of such readers. In environments with many readers and fewwriters, this technique avoids the overhead of record-level RURs most ofthe time.

When two transactions try to update the same record, traditionalconcurrency control mechanisms view this as a conflict. The general ruleis that the second attempt must wait for the completion of the firsttransaction, or one transaction must be aborted. However, there are wellknown exceptions to this general rule. A common example is the case oftwo transactions making concurrent deposits to the same bank account. Solong as neither transaction reads the balance before making the deposit,the deposits can occur in either order without affecting serializationor correctness.

The present invention supports the ability of two transactions toconcurrently modify the same field of the same record in a special“arithmetic mode”. When two or more transactions use a table inarithmetic mode, the system does not establish serialization orderingsbetween those transactions when they update records in the table. Thissupports the ability to rollback the changes made in arithmetic mode bycomputing an UNDO operation after every change. This UNDO operation isthe logical inverse of the operation made. For example, if a transactionchanged a data item in arithmetic mode by applying an “add 100”operation to its then-present-value, then it would also record a“subtract 100” UNDO operation, to be executed if and only if thetransaction aborts. Similarly if a transaction changed a data item inarithmetic mode by multiplying its then-present-value by 100, then itwould also record a “divide by 100” UNDO operation.

One embodiment of the present invention places a restriction onarithmetic mode usage. For a given field, all transactions updating thatfield in arithmetic mode are limited to performing either (a) additionsand/or subtractions, or (b) multiplications and/or divisions by anon-zero qualtity. Attempts to update a field in arithmetic mode in away that would violate this restriction are denied.

FIG. 8 is a flow chart of a procedure for establishing serial orderings.The AssertOrder procedure is used to establish arcs in a serializationgraph, and to trigger serialization cycle detection. The procedure takestwo transaction ID inputs, beforeID and afterId, and tries to assertthat transaction beforeId serializes before transaction afterId. Itoperates locally first, and then communicates with other hosts on a“need to know” basis.

The first Step 701 is a quick test to ensure that beforeId and afterIDare different. It does not make sense to assert that a transaction comesbefore itself. In the next step, Step 702, the local TxMgr 103 locatesthe beforeID and afterID transactions by iterating over its vector oftransactions 201, from the oldest transaction 203 to the newesttransaction 205, and noting the index of the transactions 300 whose TIDs301 equal beforeID and afterID. After locating the beforeID transaction,in the next Step 703 the local TxMgr 103 retrieves its bit vector offollowing transactions 312. At Step 704, the TxMgr 103 checks to see ifthe afterID transaction appears as a direct follower of the beforeIDtransaction. This check is performed by finding the index of the afterIDtransaction within the TxMgr's 103 vector of transactions 201 and thentesting the bit with this same index in the beforeID transaction's bitvector of followers. If afterID is already a direct follower ofbeforeID, no additional work is necessary.

If afterID was not already a direct follower of beforeID, then the Step705 identifies the DB-Host ID 317 of the nodes that started the beforeIDtransaction. If this differs from the ID of the host on which thisprocedure is executed, the local TxMgr 103 communicates with the host317 that started the beforeID transaction, instructing it to run thisprocedure to establish a serial ordering between beforeID and afterID(Steps 706, 707). The next step 708 identifies the DB-Host ID 317 of thenode that started the afterID transaction. If this differs from the IDof the host on which this procedure is executed, the local TxMgr 103communicates with the host 100 identified by DB-Host ID 317 that startedthe afterID transaction, instructing it to run this procedure toestablish a serial ordering between beforeID and afterID (Steps 709,710). The communication in Steps 707 and 710 are packaged as anAssertOrder message listing both beforeID and afterID, and is deliveredasynchronously to the relevant hosts. The local TxMgr 103 does not waitfor a response from either host 100. It proceeds on the optimistic basisthat no global serialization cycle will be found. If it is wrong in thisassumption, a host 100 will later abort one or more transactions toresolve the cycle.

In one preferred embodiment the communication (Steps 707 and 710)between the local TxMgr 103 and the host(s) 317 that started thebeforeID and afterID transactions is skipped. When computingenvironments are well ordered and highly predictable, a databaseadministrator may know that a given mix of applications cannot possiblyform a serialization cycle. In such cases, cycle detection isunnecessary, and communication between local nodes and hosts for thepurpose of cycle detection can therefore be skipped.

After the local host has communicated new serialization orderinginformation to remote hosts on a need-to-know basis, the next step is tointegrate the new ordering information into the local graph. At Step711, if the node executing this procedure started the beforeID orafterID transactions, it must first check serializability, by callingthe CheckSerializationCycles procedure at Step 712 (described below). Ifthis procedure finds a cycle (Step 713), it also supplies a list ofpotential victims, such that aborting one or more of these victimsshould help eliminate the cycle. In the case that a cycle was found, theTxMgr 103 selects a victim at Step 715 from the list of candidatevictims and aborts the victim at Step 716. A victim can be selectedbased on priority, desired completion time (if any), and age.Transactions can be assigned a priority class. Given a choice, thelowest priority victims are chosen. Within a priority class, the victimswhose desired completion time (if any) is furthest away are chosen. Allother things being equal, the victims that have been started mostrecently are chosen.

If there is no cycle or if there is a cycle, but the selected victim isneither the beforeID nor the afterID (Step 717), then the afterID isexplicitly listed as a follower of the beforeID transaction (Step 714).

The pseudo-code for establishing serial orderings follows:

Local Detection of Orderings by Reference to Resource Usage RecordsLocal Record of Ordering If beforeID == afterID, ignore Find theBeforeUser based on ID Get BeforeUser's set of following IdsCommunication of Serialization Arcs to Hosts If either end of the newordering is non-local Inform the host of the non-local user of theordering Integration of Arcs into Host's Graph If (CheckSerializabilityfails) Select Victim (not necessarily member of arc) Abort Victim If(Victim not either member of arc) Add afterID to beforeTX's set offollowers Else Add afterID to beforeTX's set of followers

FIG. 9 is a flow chart of a recursive procedure for serialization cycledetection (the CheckSerializability function). The procedure takes twoinputs and two parameters that are both inputs and outputs. The inputsare the TIDs of two transactions, such that the first is supposed toserialize before the second. If the algorithm determines that the secondalready serializes before the first, then the check will fail. Each timethe procedure is called recursively, the beforeID stays the same but theafterID changes. The third parameter is a set of all transactions knownto follow directly or indirectly the transaction identified by theoriginal afterID. It is initially an empty set, and accumulates thefollowers of the original afterID with each level of recursion. The lastparameter is initially an empty set. If a cycle is detected, it holdsthe identifiers of all transactions that participate in the cycle. Thesystem will use that information to select a victim to abort in order tobreak the cycle and restore linear serializability.

At Step 801 the two TIDs are compared. If the afterID is the same as thebeforeID, then a cycle has occurred; the procedure adds the afterID tothe cycleSet at Step 802 and sets the result to a value indicating thata cycle occurred (Step 803), and returns the result at Step 899. If thebeforeTID and afterTID differ, Step 804 finds the transaction 300 whoseTID field 301 contains the value for the afterID, and retrieves the setof its followers 312. Step 805 finds the new followers by subtractingthe knownSetOfFollowers (compute bitwise NOT of knownFollowers, andbitwise AND the result with set of followers of afterID). Step 806 addsthe set of followers of afterID to the set of Known Followers (computebitwise OR). At Step 807 the procedure loops through the set of newfollowers produced as a result of Step 805. For each new follower (Steps808, 809), the procedure recursively calls itself (Step 810), passingthe new follower as the new value for afterID (Step 811). If the resultof the recursive call is that a cycle was detected (Step 812), then theprocedure adds afterId to cycleMembers at Step 813 and returns anindicator that a cycle was found. Otherwise, the procedure loops back toStep 808 to check for the next new follower. If, after considering everynew follower (if any), no serialization cycles have been found, then theprocedure returns at Step 899.

The pseudocode for checking for serialization cycles follows:

CheckSerializationCycles(beforeID, afterID, knownSetOfFollowers,SetOfCycleMembers) If AfterID = beforeID add afterID to cycleSet returnvalue indicating cycle occurred Get set of followers of afterTID Findnew followers (followers & ~knownFollowers) Add followers of afterID toset of Known Followers Foreach new follower Recursively checkserializability (new Follower is new AfterID) If cycle, add afterID tocycle membersFIGS. 10, 11, and 12 are illustrative examples of how a preferredembodiment of the present invention checks for serialization cycles inthe case of one, two, or three (or more) hosts respectively. Thediagrams show the relationship of the procedures for acquiring resourceusage records, establishing serial orderings and checking forserialization cycles. The figures illustrate message passing betweenDB-Hosts and DB-SPUs.

FIG. 10 is an activity sequence diagram showing communications betweendistributed database components during serialization cycle detectionwith a single host and several SPUs. A client (Client 1) initiates adatabase transaction (TX#1) on DB Host1 and requests a read of aresource (Resource #1). The Read Resource #1 request is sent to DB SPU1which gets a Read RUR on Resource #1. Client 1 then requests an updateto a resource (Resource #2) on DB SPU2. The Update Resource #2 requestis sent to DB SPU2 which gets a Write RUR on Resource #2.

Client 2 initiates a database transaction (TX#2) on DB Host1 andrequests a write on a resource (Resource#1). The Write Resource#1request is sent to DB SPU1 which gets a Write RUR on Resource#1. At thispoint it can be determined that TX#2 must follow TX#1 and thisinformation is communicated back to the transaction host (DB Host1)because DB Host1 “needs to know” about the serialization. DB Host 1records the new dependency (TX#1→TX#2) in its serialization graph andchecks for cycles. No cycles are detected.

Client 2 then requests a read on Resource#2. The Read Resource#2 requestis sent to DB SPU2 which gets a Read RUR on Resource #2. This causes anassertion that TX#2 must follow TX#1 and this information iscommunicated back to the transaction host (DB Host1).

DB Host 1 records the new dependency, which results in a serializationorder of TX#1 before TX#2 before TX#1 (TX#1→TX#2→TX#1) in theserialization graph. This ordering represents a cycle in theserialization graph and a decision is made to abort one of thetransactions. The selected transaction is then aborted. In this waydistributed concurrency control using serialization ordering is achievedacross several SPUs and a single host.

FIG. 11 is an activity sequence diagram showing communication betweendistributed database components during serialization cycle detectionwith two hosts and several SPUs. In this example it is assumed that theserialization graph at DB Host1 contains a TX#1→TX#2 dependency and thatthe serialization graph at DB Host2 contains a TX#3→TX#4 dependency. Aclient (Client 2) initiates a query (Query5) in database transaction(TX#2) and makes a read request to DB SPU1. DB SPU1 detects a conflict,asserts a new ordering (TX#2→TX#3) and communicates this information toDB Host1 and DB Host2.

DB Host1 records the new dependency in the serialization graph(TX#1→TX#2→TX#3) and checks for cycles. No cycles currently exist. DBHost1 then checks for two or more foreign hosted transactions. Two ormore foreign hosted transactions do not currently exist. DB Host2records the new dependency in the serialization graph (TX#2→TX#3→TX#4)and checks for cycles. No cycles currently exist. DB Host2 then checksfor two or more foreign hosted transactions. Two or more foreign hostedtransactions do not currently exist.

Client 1 then initiates a query (Query6) in database transaction (TX#1)and makes a write request to DB SPU2. DB SPU2 detects a conflict,asserts a new ordering (TX#4→TX#1) and communicates this information toDB Host1 and DB Host2.

DB Host1 records the new dependency in the serialization graph(TX#4→TX#1→TX#2→TX#3) and checks for cycles. No cycles currently exist.DB Host1 then traverses the serialization graph and detects TX#3 andTX#4 as transactions initiated by foreign hosts. DB Host1 then sends itslocal ordering (TX#1→TX#2) to the host(s) of TX#3 and TX#4 (DB Host2).DB Host2 records the new dependency in the serialization graph(TX#2→TX#3→TX#4→TX#1) and checks for cycles. No cycles currently exist.DB Host2 then traverses the serialization graph and detects TX#1 andTX#2 as transactions initiated by foreign hosts. DB Host2 then sends itslocal ordering (TX#3→TX#4) to the host(s) of TX#1 and TX#2 (DB Host1).

DB Host2 now adds the new dependency from DB Host1 (TX#1→TX#2) to itsserialization graph (TX#2→TX#3→TX#4→TX#1) to produce(TX#1→TX#2→TX#3→TX#4→TX#1). A check for cycles is performed and a cycleis now detected. A transaction to be aborted is selected in order toremove the cycle. The victim transaction, if local, is aborted. If thevictim transaction is not local, a message may be sent to its host. Inthis way distributed concurrency control using serialization ordering isachieved across several SPUs and two hosts.

FIG. 12 is an activity sequence diagram showing communication betweendistributed database components during serialization cycle detectionwith three hosts and several SPUs. In this example it is assumed thatthe serialization graph at DB Host1 contains a TX#1→TX#2 dependency,that serialization graph at DB Host2 contains a TX#3→TX#4 dependency,and that the serialization graph at DB Host 3 contains a TX#5→TX#6dependency. A client (Client 2) initiates a query (Query7) in databasetransaction (TX#2) and makes a read request to DB SPU1 . DB SPU1 detectsa conflict, asserts a new ordering (TX#2→TX#3) and communicates thisinformation to DB Host1 and DB Host2.

DB Host1 records the new dependency in the serialization graph(TX#1→TX#2→TX#3) and checks for cycles. No cycles currently exist. DBHost1 then checks for two or more foreign host transactions. Since onlyTX#3 is foreign to DB Host1 , two or more foreign hosted transactions donot currently exist. DB Host2 records the new dependency in theserialization graph (TX#2→TX#3→TX#4) and checks for cycles. No cyclescurrently exist. DB Host2 then checks for two or more foreign hosttransactions. Since only TX#2 is foreign to DB Host2 , two or moreforeign hosted transactions do not currently exist.

Client 4 then initiates a query (Query8) in database transaction (TX#4)and makes a read request to DB SPU2 . DB SPU2 detects a conflict,asserts a new ordering (TX#4→TX#5) and communicates this information toDB Host2 and DB Host3.

DB Host2 records the new dependency in the serialization graph(TX#2→TX#3→TX#4→TX#5) and checks for cycles. No cycles currently exist.DB Host2 then traverses the serialization graph and detects TX#2 andTX#5 as transactions initiated by foreign hosts. DB Host2 then sends itslocal ordering (TX#3→TX#4→TX#5) to the host(s) of TX#1 (DB Host1) andTX#5 (DB Host3). DB Host3 records the new dependency in theserialization graph (TX#2→TX#3→TX#4→TX#5→TX#6) and checks for cycles. Nocycles currently exist.

DB Host1 now adds the new dependency from DB Host2 (TX#3→TX#4→TX#5) toits serialization graph to produce (TX#1→TX#2→TX#3→TX#4→TX#5). A checkfor cycles is performed and a cycle is not detected.

Client 1 then initiates a query (Query9) in database transaction (TX#1)and makes a write request to DB SPU3 . DB SPU3 detects a conflict,asserts a new ordering (TX#6→TX#1) and communicates this information tothe host of TX#1 (DB Host1) and the host of TX#6 (DB Host3). DB Host1records the new dependency in the serialization graph(TX#6→TX#1→TX#2→TX#3→TX#4→TX#5) and checks for cycles. No cyclescurrently exist. DB Host3 records the new dependency in theserialization graph (TX#2→TX#3→TX#4→TX#5→TX#6→TX#1) and checks forcycles. No cycles currently exist. DB Host1 checks for two or moreforeign host transactions and detects TX#6 and TX#3 as foreign hostedtransactions. DB Host1 transmits its local serialization order(TX#1→TX#2) to DB Host3 . In this way distributed concurrency controlusing serialization ordering is achieved across several SPUs and threehosts.

FIG. 13 is a flow chart of a procedure for releasing Resource UsageRecords. Resources used by a transaction that is rolled back can bereleased immediately, since that transaction will be removed from theserialization graph as if it never happened. The resources used by acommitted transaction can be released when it is certain that no newserialization edges can be created that would lead to a cycle (ofserialization edges) involving this transaction. This will be true whenthere is no other active transaction that started before thistransaction committed, and there is no other committed transaction thatserializes before this transaction. A potential opportunity to clear outresources used by committed or aborted transactions therefore ariseswhenever the “oldest active” transaction commits or aborts.

The procedure takes as input a pointer to a transaction 300 beingcommitted or aborted. Step 901 locates the transaction corresponding tothe TxMgr data structure's 200 Oldest Active Transaction field 204. Ifthese are different at Step 902, the procedure returns at Step 999. Ifthese are equal at Step 902, then the process is completing the oldestactive transaction. First, the process finds the new “oldest active”transaction at Step 903 and sets oldestActiveIndex to point to thistransaction (if there is one, or a special “NONE” value if there are nolonger any active transactions) at Step 904. Then, at Step 905, a loopis performed over each transaction 300 on the TxMgr's vector oftransactions 201, starting with the oldestIndex 203 up to but notincluding the (new) oldestActiveIndex 204, calling the next suchtransaction “nextTX”. In Step 906, nextTX is tested to see if there areany active transactions whose Transaction Start Time 307 is earlier thanthe nextTX's Transaction Start Time 307. If there are such transactions,the nextTX is not cleaned up, and the process loops back to Step 905.If, still at Step 906, some other committed transaction 300 serializesbefore nextTX, then the next TX is not cleaned up, and the process loopsback to Step 905. Otherwise, there are no committed transactions thatserialize before nextTX, and there no active transactions that startedbefore nextTX, in which case, we may release the resources associatedwith nextTX, starting with Steps 908 through 910, in which the processremoves the RURs (whose Resource UserID 402 matches the nextTX) from theResource Usage Record Index 510, and frees the memory associated withany such RURs. Then, at Step 911, the procedure frees the memoryassociated with nextTX. At Step 912 the Transaction Manager's CurrentNumber of Transactions field (202) is decremented by one. If the indexof nextTX was equal to the Transaction Manager's Index of the OldestTransaction in the Vector (203) field, then reset the value of thisindex (203) to the index of the next transaction in the vector (Steps913, 914).

The pseudo-code for releasing resource of a completed transactionfollows:

ReleaseResources (Transaction being committed or aborted) Find theoldest active transaction If the oldest active transaction is not thesame as the input Return without doing anything; Find the new oldestactive transaction Set the oldestActive transaction to the new value Foreach transaction, starting with the oldestIndex, up to but not includingthe oldestActiveIndex If there is no active transaction that startedbefore this trans- action and there is no transaction that serializesbefore this trans- action, Cleanup its ResourceUsageRecords by doing thefollowing: Foreach Resource Class Find the associated ResourceUsageTree510 Remove the RUR if any of this TX from the Tree Release memoryassociated with the next transaction Decrement thecurrentNumberOfTransactions If this is the oldest transaction, set theoldestIndex to the next transaction

FIG. 14 is a flow chart of a procedure for awakening waitingtransactions. When a transaction commits or aborts, the TxMgr (103, 113)uses Transaction Manager data structure 200 to check whether it shouldawaken any transactions that were waiting for the completed transactionto finish using its resources. The procedure operates by looping over(repeating Steps 1001 through 1004 for) the vector of transactions 201from oldest, using index 204, to newest using index 205. For each suchtransaction 300, TxMgr (103, 113) checks to see if it is waiting for thecompletion of the input transaction (Step 1002). If so, it clears itstransaction waiting field 315, and restarts the waiter's query plan 316(Steps 1003, 1004). After each such transaction has been so processed,the procedure ends (returns) at Step 1099.

The pseudo-code for awakening a waiting transaction follows:

Upon Commit/Abort Foreach transaction If nextTX is waiting for commiterSet the state of the TX to not waiting Restart the query plan's intendeduse

While this invention has been particularly shown and described withreferences to preferred embodiments thereof, it will be understood bythose skilled in the art that various changes in form and details may bemade therein without departing from the scope of the inventionencompassed by the appended claims.

1. A computer implemented method of establishing serialization orderingamong database transactions in a database system, comprising: given adatabase transaction, storing a representation of an intended use of adatabase resource by the database transaction, wherein intended use ofthe database resource includes any of reads and writes based on historyof the database transaction, wherein the intended use of the databaseresource includes at least one of: accessing state of the databaseresource and modifying the state of the database resource, and whereinstoring the representation of the intended use of database resourcecomprises: creating a resource usage record; and associating theresource usage record with the database transaction, wherein theresource usage record is maintained for a period of time after thedatabase transaction commits, and is not released until no serializationorderings is created that would involve the committed databasetransaction in a cycle of serialization orderings; and dynamicallyasserting serialization ordering between the database transaction andother database transactions based upon the intended use of the databaseresource by the database transaction and the other databasetransactions.
 2. The method of establishing serialization ordering ofclaim 1, wherein the serialization ordering between a first transactionaccessing the state of the database resource and a second transactionmodifying the state of the database resource is in the first transactionpreceding the second transaction.
 3. The method of establishingserialization ordering of claim 2, wherein the serialization orderingimposed applies whether the first transaction accessed the state of thedatabase resource temporally before or temporally after the secondtransaction modifies the state of the database resource.
 4. The methodof establishing serialization ordering of claim 1, wherein theserialization ordering imposed when a first database transactionaccesses or modifies the database resource and a second databasetransaction modifies or accesses the database resource is that thesecond database transaction waits for the first database transaction tocomplete.
 5. The method of establishing serialization ordering of claim1, wherein the resource usage record is released when the databasetransaction is aborted or rolled back.
 6. The method of establishingserialization ordering of claim 1, wherein the database resourceincludes at least one of the following: a record, a subset of records ina table identified by a qualifier on contents of records, and a table ofrecords in a relational database system.
 7. The method of establishingserialization ordering of claim 1, wherein the database system operatesin conjunction with serial ordering of database transactions, such thata first database transaction having a serialization ordering before asecond database transaction can access versions of the databaseresource, which existed before any modifications made by the seconddatabase transaction.
 8. The method of establishing serializationordering of claim 1, wherein the use of the database resource comprisesmodifying the state of the database resource with a commutativeoperation.
 9. The method of establishing serialization ordering of claim8, wherein the commutative operation is at least one of: addition,subtraction, multiplication and division.
 10. The method of establishingserialization ordering of claim 8, wherein no serialization ordering isimposed when a plurality of database transactions operate to modify thedatabase resource using arithmetically commutative operations.
 11. Themethod of establishing serialization ordering of claim 1, wherein theserialization ordering imposed when a first database transaction commitsbefore a second database transaction starts.
 12. The method ofestablishing serialization ordering of claim 1, wherein the step ofstoring a representation of the use does not assert the serializationordering when at least one or any combination of the following occurs:(a) the database transaction intends only to create or insert newresources, (b) the database transaction modifying the database resourceacquired the database resource in reverse mode, (c) the use of thedatabase resource does not require concurrency control, and (d) thedatabase resource is unable be shared by another database transaction.13. A computer system for establishing serialization orderings amongdatabase transactions in a database system, comprising: at least twotransactions; a representation of intended use of a database resource bythe at least two transactions, wherein intended use of the databaseresource includes any of reads and mites based on history of thedatabase transaction, wherein the intended use of the database resourceincludes at least one of: accessing state of the database resource andmodifying the state of the database resource, and wherein storing therepresentation of the intended use of database resource comprises:creating a resource usage record; and associating the resource usagerecord with the database transaction, wherein the resource usage recordis maintained for a period of time after the database transactioncommits, and is not released until no serialization orderings can becreated that would involve the committed database transaction in a cycleof serialization orderings; and a serialization graph storing anordering between the at least two database transactions based upon theintended use of the database resource by the at least two databasetransactions.
 14. A system as claimed in claim 13, wherein the databasesystem is a multi-versioning database system.